﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;

namespace DataTableToEntityClass
{
    public class DAL:IDAL
    {
       
        /// <summary>
        /// 获取服务器上所有数据库名
        /// </summary>
        /// <param name="conStr"></param>
        /// <returns></returns>
        public List<TempModel> GetDataBaseList(string conStr)
        {
            List<TempModel> result = new List<TempModel>();
            string sql = "Select Name From Master..SysDatabases order By Name";
            DataSet ds = SqlHelper.sqlSelectDS(conStr, CommandType.Text, sql);
            foreach(DataRow row in ds.Tables[0].Rows)
            {
                result.Add(new TempModel { Name = row["Name"].DBValueToTypeValue<string>() ?? "", Type = "DataBase" });
            }
            return result;
        }
        /// <summary>
        /// 获取指定数据库的所有表名
        /// </summary>
        /// <param name="conStr"></param>
        /// <returns></returns>
        public List<TempModel> GetTableList(string conStr)
        {
            List<TempModel> result = new List<TempModel>();
            string sql = $"Select Name From sysobjects Where XType='U' order By Name";
            DataSet ds = SqlHelper.sqlSelectDS(conStr, CommandType.Text, sql);
            foreach (DataRow row in ds.Tables[0].Rows)
            {
                result.Add(new TempModel { Name = row["Name"].DBValueToTypeValue<string>() ?? "", Type = "Table" });
            }
            return result;
        }

        /// <summary>
        /// 获取指定表的所有字段
        /// </summary>
        /// <param name="conStr"></param>
        /// <param name="table"></param>
        /// <returns></returns>
        public List<TempModel> GetTableColumns(string conStr, string table)
        {
            List<TempModel> result = new List<TempModel>();
            string sql = $@"Select SysColumns.name Name,systypes.name Type,extended_properties.value Description From SysColumns 
                            left join systypes on SysColumns.xtype=systypes.xusertype
                            left join sys.extended_properties ON  SysColumns.id = extended_properties.major_id AND SysColumns.colid = extended_properties.minor_id  
                            Where id = Object_Id(@table)";
            DataSet ds = SqlHelper.sqlSelectDS(conStr, CommandType.Text, sql,new SqlParameter("@table",table));
            foreach (DataRow row in ds.Tables[0].Rows)
            {
                result.Add(new TempModel
                {
                    Name = row["Name"].DBValueToTypeValue<string>() ?? "",
                    Type = row["Type"].DBValueToTypeValue<string>() ?? "",
                    Description = row["Description"].DBValueToTypeValue<string>() ?? ""
                });
            }
            return result;
        }
    }
}
